using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Spread.Common.Features.Sample;
using FarPoint.Win.Spread;
using FarPoint.Win.Spread.CellType;

namespace Spread.Calculation.Features
{
    public partial class ArrayFormulasDemo : BaseSampleControl
    {
        public ArrayFormulasDemo()
        {
            InitializeComponent();
            InitSpreadSheet();
        }
        private void InitSpreadSheet()
        {
            Name = "Array Formulas";
            sheetView.Rows.Default.Height = 25;
            sheetView.Columns.Default.Width = 90;

            sheetView.SetClip(0, 0, 1, 6, "Customer\tProduct\tSale number ( items)\tPrice ($)\tTotal no tax ($)\tTotal with tax ($)");
            sheetView.SetClip(1, 0, 8, 2, "Mike\tApple\nSally\tLemon\nNancy\tLemon\nJane\tLemon\nSarah\tApple\nLucy\tApple\nEric\tLemon\nTom\tApple");
            sheetView.Cells["C2:C9"].FormulaArray = "{2;10;100;19;34;15;20;14}";
            sheetView.Cells["D2:D9"].Formula = @"IFS(B2=""Apple"",10,B2=""Lemon"",20)";

            sheetView.Cells["D2"].Note = @"Excel 2016 doesn't support the IFS function unless you have the Office 365 subscrition and suffuciently updated Excel version installed, so if want to export to older versions of Excel, use nested IF functions instead: IF(B2=""Apple"",10,(IF(B2=""Lemon"",20,"""")))";
            sheetView.Cells["E2:E9"].Formula = "C2*D2";
            sheetView.Cells["F2:F9"].FormulaArray = "C2:C9*D2:D9*1.08";
            sheetView.Cells["E10"].Text = "Totals";
            sheetView.Cells["F10"].Formula = "SUM(F2:F9)";

            sheetView.Cells["D2:F9"].CellType = new CurrencyCellType();
            sheetView.Cells["F10"].CellType = new CurrencyCellType();
            sheetView.Cells["A1:F1"].BackColor = Color.LightBlue;
            sheetView.Cells["A1:F1"].ForeColor = Color.Navy;
            sheetView.Cells["A2:B9"].BackColor = Color.LightGreen;
            sheetView.Cells["F2:F10"].BackColor = Color.LightGreen;
            sheetView.Cells["E10"].BackColor = Color.LightGreen;
            sheetView.Cells["C2:E9"].BackColor = Color.LightYellow;

            sheetView.Columns[2].Width = 110;
            spread.DefaultSkin = DefaultSpreadSkins.Office2016Colorful;
            sheetView.DefaultStyle.NoteIndicatorSize = new Size(7, 7);
            spread.CellNoteIndicatorRenderer = new TriangleNoteIndicatorRenderer();
        }
    }
}


//
//ArrayFormulasDemo.Designer.cs
//


namespace Spread.Calculation.Features
{
    partial class ArrayFormulasDemo
    {
        /// <summary> 
        /// Required designer variable.
        /// </summary>
        private System.ComponentModel.IContainer components = null;

        /// <summary> 
        /// Clean up any resources being used.
        /// </summary>
        /// <param name="disposing">true if managed resources should be disposed; otherwise, false.</param>
        protected override void Dispose(bool disposing)
        {
            if (disposing && (components != null))
            {
                components.Dispose();
            }
            base.Dispose(disposing);
        }

        #region Component Designer generated code

        /// <summary> 
        /// Required method for Designer support - do not modify 
        /// the contents of this method with the code editor.
        /// </summary>
        private void InitializeComponent()
        {
            this.panelFormulaBar = new System.Windows.Forms.Panel();
            this.splitContainer1 = new System.Windows.Forms.SplitContainer();
            this.nameBox1 = new FarPoint.Win.Spread.NameBox();
            this.formulaTextBox1 = new FarPoint.Win.Spread.FormulaTextBox();
            this.spread = new FarPoint.Win.Spread.FpSpread(FarPoint.Win.Spread.LegacyBehaviors.None);
            this.sheetView = new FarPoint.Win.Spread.SheetView();
            this.pnlMain.SuspendLayout();
            ((System.ComponentModel.ISupportInitialize)(this.spread)).BeginInit();
            ((System.ComponentModel.ISupportInitialize)(this.sheetView)).BeginInit();
            this.SuspendLayout();
            // 
            // pnlMain
            // 
            this.pnlMain.Controls.Add(this.spread);
            this.pnlMain.Controls.Add(this.panelFormulaBar);
            // 
            // panelFormulaBar
            // 
            this.panelFormulaBar.Controls.Add(this.splitContainer1);
            this.panelFormulaBar.Dock = System.Windows.Forms.DockStyle.Top;
            this.panelFormulaBar.Location = new System.Drawing.Point(0, 24);
            this.panelFormulaBar.Name = "panelFormulaBar";
            this.panelFormulaBar.Size = new System.Drawing.Size(800, 23);
            this.panelFormulaBar.TabIndex = 6;
            // 
            // splitContainer1
            // 
            this.splitContainer1.Dock = System.Windows.Forms.DockStyle.Fill;
            this.splitContainer1.Location = new System.Drawing.Point(0, 0);
            this.splitContainer1.Name = "splitContainer1";
            // 
            // splitContainer1.Panel1
            // 
            this.splitContainer1.Panel1.Controls.Add(this.nameBox1);
            this.splitContainer1.Panel1MinSize = 121;
            // 
            // splitContainer1.Panel2
            // 
            this.splitContainer1.Panel2.Controls.Add(this.formulaTextBox1);
            this.splitContainer1.Size = new System.Drawing.Size(800, 23);
            this.splitContainer1.SplitterDistance = 121;
            this.splitContainer1.TabIndex = 0;
            // 
            // nameBox1
            // 
            this.nameBox1.Dock = System.Windows.Forms.DockStyle.Fill;
            this.nameBox1.Location = new System.Drawing.Point(0, 0);
            this.nameBox1.MaxLength = 255;
            this.nameBox1.Name = "nameBox1";
            this.nameBox1.Size = new System.Drawing.Size(121, 21);
            this.nameBox1.TabIndex = 0;
            // Attach nameBox1 to spread
            this.nameBox1.Attach(this.spread);
            // 
            // formulaTextBox1
            // 
            this.formulaTextBox1.Dock = System.Windows.Forms.DockStyle.Fill;
            this.formulaTextBox1.Location = new System.Drawing.Point(0, 0);
            this.formulaTextBox1.Multiline = false;
            this.formulaTextBox1.Name = "formulaTextBox1";
            this.formulaTextBox1.Size = new System.Drawing.Size(675, 23);
            this.formulaTextBox1.TabIndex = 0;
            // Attach formulaTextBox1 to spread
            this.formulaTextBox1.Attach(this.spread);
            // 
            // spread
            // 
            this.spread.AccessibleDescription = "spread, BasicFunction, Row 0, Column 0";
            this.spread.Dock = System.Windows.Forms.DockStyle.Fill;
            this.spread.Location = new System.Drawing.Point(0, 0);
            this.spread.Name = "spread";
            this.spread.Sheets.AddRange(new FarPoint.Win.Spread.SheetView[] {
            this.sheetView});
            this.spread.Size = new System.Drawing.Size(477, 487);
            this.spread.TabIndex = 0;
            // 
            // sheetView
            // 
            this.sheetView.Reset();
            this.sheetView.SheetName = "ArrayFormulas";
            // Formulas and custom names must be loaded with R1C1 reference style
            this.sheetView.ReferenceStyle = FarPoint.Win.Spread.Model.ReferenceStyle.R1C1;
            this.sheetView.RowHeader.Columns.Default.Width = 35F;
            this.sheetView.ReferenceStyle = FarPoint.Win.Spread.Model.ReferenceStyle.A1;
            // 
            // ArrayFormulasDemo
            // 
            this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 13F);
            this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
            this.Name = "ArrayFormulasDemo";
            this.pnlMain.ResumeLayout(false);
            ((System.ComponentModel.ISupportInitialize)(this.spread)).EndInit();
            ((System.ComponentModel.ISupportInitialize)(this.sheetView)).EndInit();
            this.ResumeLayout(false);

        }

        #endregion

        private System.Windows.Forms.SplitContainer splitContainer1;
        protected internal System.Windows.Forms.Panel panelFormulaBar;
        protected internal FarPoint.Win.Spread.NameBox nameBox1;
        protected internal FarPoint.Win.Spread.FormulaTextBox formulaTextBox1;
        private FarPoint.Win.Spread.FpSpread spread;
        private FarPoint.Win.Spread.SheetView sheetView;
    }
}
